package utils;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;

public class DBUtils {

	private final String userName = "root";
	private final String passWord = "root";
	private final String driver = "com.mysql.jdbc.Driver";
	private final String url = "jdbc:mysql://127.0.0.1:3306/iteaching_db";
	private Connection connection = null;
	private PreparedStatement pstmt;
	private ResultSet resultSet;
	private static DBUtils instance;

	public DBUtils() {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void getConnection() {
		try {
			connection = (Connection) DriverManager.getConnection(url,
					userName, passWord);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static DBUtils getInstance() {
		if (instance == null) {
			instance = new DBUtils();
		}
		return instance;
	}

	/**
	 * release database connection
	 */
	public void releaseConnection() {
		if (resultSet != null) {
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		if (connection != null) {
			try {
				connection.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
	}

	/**
	 * update OR delete from database
	 * 
	 * @param sql
	 *            sql statement
	 * @param params
	 *            params of sql
	 * @return
	 * @throws SQLException
	 */
	public boolean updateOrDelSQL(String sql, List<Object> params)
			throws SQLException {
		boolean flag = false;
		pstmt = (PreparedStatement) connection.prepareStatement(sql);
		int index = 1;
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		int count = pstmt.executeUpdate();
		flag = count > 0 ? true : false;
		return flag;
	}

	public Map<String, Object> queryOneMap(String sql, List<Object> params)
			throws SQLException {
		Map<String, Object> map = new HashMap<String, Object>();
		pstmt = (PreparedStatement) connection.prepareStatement(sql);
		int index = 1;
		if (params != null && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i++) {
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet = pstmt.executeQuery();
		ResultSetMetaData metaData = (ResultSetMetaData) resultSet
				.getMetaData();
		int col_len = metaData.getColumnCount();
		while (resultSet.next()) {
			for (int i = 0; i < col_len; i++) {
				String key = metaData.getColumnName(i + 1);
				Object value = resultSet.getObject(key);
				if (value == null) {
					value = "";
				}
				map.put(key, value);
			}
		}
		return map;
	}
	public List<Map<String,Object>> queryMultMap(String sql,List<Object> params) throws SQLException{
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
		pstmt=(PreparedStatement) connection.clientPrepareStatement(sql);
		int index=1;
		if(params!=null&&!params.isEmpty()){
			for(int i=0;i<params.size();i++)
			{
				pstmt.setObject(index++, params.get(i));
			}
		}
		resultSet=pstmt.executeQuery();
		ResultSetMetaData metaData=(ResultSetMetaData) resultSet.getMetaData();
		int cols_len=metaData.getColumnCount();
		while(resultSet.next()){
			Map<String,Object> map=new HashMap<String,Object>();
			for(int i=0;i<cols_len;i++){
				String key=metaData.getColumnName(i+1);
				Object value=resultSet.getObject(key);
				if(value==null){
					value="";
				}
				map.put(key, value);
			}
			list.add(map);
		}
		return list;
	}
}
